package test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BlogController {

	private List<Topic> listTopic = new ArrayList<Topic>();
	private PreparedStatement pstm = null;
	private ResultSet rs = null;

	public List<Topic> getBlogList() {

		ConnectionManager cm = ConnectionManager.getInstance();

		// コネクション取得
		Connection con = cm.getConn();

		// SQLの生成
		StringBuilder sql = new StringBuilder();

		sql.append("SELECT ");
		sql.append(" NO, ");
		sql.append(" TITLE, ");
		sql.append(" MESSAGE, ");
		sql.append(" TIME, ");
		sql.append(" USER_NAME ");
		sql.append("FROM ");
		sql.append(" BLOGTOPIC ");
		sql.append("ORDER BY");
		sql.append(" NO DESC");

		try {
			// PreparedStatementの生成
			pstm = con.prepareStatement(sql.toString());

			// SQLを実行
			rs = pstm.executeQuery();

			Topic tptmp = new Topic();

			// 結果格納
			while (rs.next()) {
				tptmp = new Topic();
				tptmp.setTitle(rs.getString("TITLE"));
				tptmp.setMessage(rs.getString("MESSAGE"));
				tptmp.setTime(rs.getString("TIME"));
				tptmp.setUser(rs.getString("USER_NAME"));
				listTopic.add(tptmp);
			}

			con.commit();

		} catch (SQLException e) {
			System.err.println("SQLエラーです。");
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstm != null) {
					pstm.close();
				}
			} catch (SQLException e) {
				System.err.println("クローズに失敗");
			}
		}

		return listTopic;
	}

	public void postBlogList(Topic addtpic) {

		ConnectionManager cm = ConnectionManager.getInstance();

		// コネクション取得
		Connection con = cm.getConn();

		// SQLの生成
		StringBuilder sql = new StringBuilder();

		sql.append("INSERT INTO ");
		sql.append(" BLOGTOPIC");
		sql.append(" ( ");
		sql.append(" NO, ");
		sql.append(" TITLE, ");
		sql.append(" MESSAGE, ");
		sql.append(" TIME, ");
		sql.append(" USER_NAME ");
		sql.append(" ) ");
		sql.append("VALUES ");
		sql.append(" ( ");
		sql.append(" (SELECT MAX(NO)+1 FROM BLOGTOPIC),");
		sql.append("  ? ,");
		sql.append("  ? ,");
		sql.append(" SYSTIMESTAMP, ");
		sql.append(" 'test' ");
		sql.append(" ) ");

		try {
			// PreparedStatementの生成
			pstm = con.prepareStatement(sql.toString());

			// 引数で渡された文字列をバインド
			pstm.setString(1, addtpic.getTitle());
			pstm.setString(2, addtpic.getMessage());

			// SQLを実行
			pstm.executeUpdate();

			con.commit();

		} catch (SQLException e) {
			System.err.println("SQLエラーです。");
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstm != null) {
					pstm.close();
				}
			} catch (SQLException e) {
				System.err.println("クローズに失敗");
			}
		}

		return;
	}

}
